/*PROCEDURES*/

/*eisodos to id tis kathgoria kai e3odos oles oi gonikes tis*/
DROP PROCEDURE IF EXISTS get_categ_chain;
DELIMITER $

CREATE PROCEDURE get_categ_chain(IN catid INT)
BEGIN

	DECLARE cat_name VARCHAR(20);
	DECLARE par_id INT;

	SELECT fid INTO par_id FROM category WHERE id=catid;
	SELECT title INTO cat_name FROM category WHERE id=catid;

	WHILE par_id IS NOT NULL
	DO
		SELECT cat_name;
		SELECT title INTO cat_name FROM category WHERE id=par_id;
		SELECT fid INTO par_id FROM category WHERE id=par_id;

	END WHILE;

	SELECT cat_name;
END$
DELIMITER ;

/*eisodos to username tou xristi kai e3odos ka8e kathgoria kai tis suntages pou exei sunta3ei gia ka8emia*/
DROP PROCEDURE IF EXISTS recipe_by_editor;
DELIMITER $
CREATE PROCEDURE recipe_by_editor(IN user_name VARCHAR(20))
BEGIN
	
	DECLARE catid INT(11);
	DECLARE rec_title VARCHAR(20);
	DECLARE cat_title VARCHAR(20);
	DECLARE not_found_message INT;

	DECLARE user_cursor CURSOR FOR
	SELECT category.title,recipe.title FROM recipe 
	INNER JOIN category ON cat_id=id
	WHERE username = user_name;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_message=1;
	
	OPEN user_cursor;
	SET not_found_message=0;
	REPEAT
		FETCH user_cursor INTO cat_title,rec_title;
		IF (not_found_message=0)
			THEN select cat_title,rec_title;
		END IF;
	UNTIL (not_found_message=1)
	END REPEAT;
	CLOSE user_cursor;

END
$
DELIMITER ;

/*eisodos to id tis katigorias pros diagrafi kai prin tin diagrapsei metaferei ta paidia tis stin goniki tis*/
DELIMITER $
DROP PROCEDURE IF EXISTS del_cat$
CREATE PROCEDURE del_cat (IN categ_id INT)
BEGIN
	DECLARE paren_id INT;
	
	SELECT fid INTO paren_id FROM category WHERE id=categ_id;
	UPDATE category SET fid=paren_id WHERE fid=categ_id;
	DELETE FROM category WHERE id =categ_id;

END
$
DELIMITER ;

/*TRIGGERS*/

/*kata ti diagrafi xristi elegxei an einai suntaktis kai an einai apotrepei ti diagrafi*/
DROP TRIGGER IF EXISTS del_user;

DELIMITER $

CREATE TRIGGER del_user BEFORE DELETE ON user
FOR EACH ROW 
BEGIN

	DECLARE editor_id varchar(20);

	SET editor_id=NULL;
	SELECT username INTO editor_id FROM editor WHERE username=OLD.username;

	IF(editor_id IS NOT NULL)
	THEN
		INSERT INTO editor(username) VALUES (NULL);
	END IF;
END$

DELIMITER ;

/*elegxos eisagwgis ba8mologias entos oriwn 1-10*/
DROP TRIGGER IF EXISTS check_rating;

DELIMITER $

CREATE TRIGGER check_rating BEFORE INSERT ON rating
FOR EACH ROW 
BEGIN

	IF(NEW.grade<0)
	THEN
		SET NEW.grade=1;
	ELSEIF(NEW.grade>10)
	THEN 
		SET NEW.grade=10;
	END IF;
END$

DELIMITER ;